package com.example.springboot.mapper;

import com.example.springboot.entity.Applications;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ApplicationsMapper {

    @Select("SELECT * FROM applications")
    List<Applications> findAll();

    @Select("SELECT * FROM applications WHERE application_id = #{application_id}")
    Applications findById(int application_id);

    @Select("SELECT * FROM applications WHERE student_id = #{student_id}")
    List<Applications> findByStudentId(@Param("student_id") String studentId);

    @Insert("INSERT INTO applications (job_id, student_id, application_status, applied_at, updated_at) VALUES (#{job_id}, #{student_id}, #{application_status}, #{applied_at}, #{updated_at})")
    @Options(useGeneratedKeys = true, keyProperty = "application_id")
    void insert(Applications application);

    @Update("UPDATE applications SET job_id=#{job_id}, student_id=#{student_id}, application_status=#{application_status}, applied_at=#{applied_at}, updated_at=#{updated_at} WHERE application_id=#{application_id}")
    void update(Applications application);

    @Delete("DELETE FROM applications WHERE application_id=#{application_id}")
    void delete(int application_id);

    @Update("UPDATE applications SET application_status = #{application_status}, updated_at = NOW() WHERE application_id = #{application_id}")
    void updateStatus(@Param("application_id") int applicationId, @Param("application_status") String applicationStatus);

    @Select("SELECT\n" +
            "\tstudents.student_id, \n" +
            "\tstudents.user_id, \n" +
            "\tstudents.`name`, \n" +
            "\tstudents.dob, \n" +
            "\tstudents.gender, \n" +
            "\tstudents.phone, \n" +
            "\tstudents.address, \n" +
            "\tstudents.resume, \n" +
            "\tstudents.education, \n" +
            "\tapplications.application_id, \n" +
            "\tapplications.job_id, \n" +
            "\tapplications.application_status, \n" +
            "\tapplications.applied_at, \n" +
            "\tjobs.company_id, \n" +
            "\tjobs.job_title\n" +
            "FROM\n" +
            "\tstudents\n" +
            "\tRIGHT JOIN\n" +
            "\tapplications\n" +
            "\tON \n" +
            "\t\tstudents.student_id = applications.student_id\n" +
            "\tRIGHT JOIN\n" +
            "\tjobs\n" +
            "\tON \n" +
            "\t\tapplications.job_id = jobs.job_id")
    List<Applications> getAllApplications();


    @Insert("INSERT INTO applications (job_id, student_id, application_status, applied_at) VALUES (#{job_id}, #{student_id}, #{application_status}, NOW())")
    void applyInsert(Applications applications);

    @Update("UPDATE applications SET application_status=#{applicationStatus} WHERE application_id=#{applicationId}")
    void saveApplicationN(int applicationId, String applicationStatus);
}